use master
create database TestDB
on
	(
	name='TestDB',
	filename='F:\数据库文件\TestDB.mdf',
	size=5,
	maxsize=9,
	filegrowth=3
	)
log on
	(
	name='TestDB_log',
	filename='F:\数据库文件\TestDB_log.ldf',
	size=5,
	maxsize=9,
	filegrowth=3
	)
	go
	use TestDB
create table typeInfo
(
typeld int primary key identity(1,1),
typeName varchar(10) not null
)

create table loginInfo
(
LoginId int primary key identity(1,1),
LoginName varchar(10) unique not null,
LoginPwd varchar(20) default('123456') not null,
LoginSex nvarchar(1) default('男') check(LoginSex='男' or LoginSex='女'),
LoginBirthday date not null,
LoginType varchar(10) not null
)

go
	use master
create database company
on
	(
	name='company',
	filename='F:\数据库文件\company.mdf',
	size=5,
	maxsize=9,
	filegrowth=3
	)
log on
	(
	name='company_log',
	filename='F:\数据库文件\company_log.ldf',
	size=5,
	maxsize=9,
	filegrowth=3
	)
	go
	use company
	create table sectionInfo
(
sectionID  int primary key identity(1,1) not null,
sectionName varchar(10) not null
)

create table userInfo
(
userNo int primary key identity(1,1) not null,
userName varchar(10) unique check(userName>4) not null,
userSex varchar(2) check(userSex='男' or userSex='女') not null,
userAge int check(userAge>=1 and userAge<=100) not null,
userAddress varchar(50) default('湖北') not null,
userSection int not null
)
 alter table userInfo
add constraint FK_sectionID_userSection foreign key(userSection) references sectionInfo(sectionID)

create table workInfo
(
workId int primary key identity(1,1) not null,
userId  int not null,
workTime datetime not null,
workDescription  varchar(40) check(workDescription='迟到' or workDescription='早退' or workDescription='旷工' or workDescription='病假'or workDescription='事假') not null
)

go
use master
create database 学生管理系统
on
	(
	name='学生管理系统',
	filename='F:\数据库文件\学生管理系统.mdf',
	size=5,
	maxsize=9,
	filegrowth=3
	)
log on
	(
	name='company_log',
	filename='F:\数据库文件\学生管理系统_log.ldf',
	size=5,
	maxsize=9,
	filegrowth=3
	)

	go
	use 学生管理系统
	create table ClassInfo
	(
	ClassID int primary key identity(1,1) ,
	ClassName nvarchar(10) unique not null,
	OpenTime date not null,
	ClassDescription nvarchar(100)
	)

	create table StudentInfo
	(
	StuID int primary key identity(1,1),
	StuName nvarchar(20) check(StuName>=2) not null,
	StuSex varchar(2) default('男') check(StuSex='男' or StuSex='女'),
	StuAge int check(StuAge>=15 and StuAge<=40) not null,
	StuAddress varchar(200) default('湖北武汉')	,
	ClassID int 
	)

	create table Course
	(
	CourseID int primary key identity(1,1),
	CourseName varchar(20) unique not null,
	CourseDescription varchar(200) 
	)

	create table Score
	(
	ScoreID int primary key identity(1,1),
	StuID int not null,
	ClassID int not null,
	Score int check(Score>=1 and Score<=100) 
	)
	go
	alter table Score
add constraint FK_StudentInfo_Score foreign key(StuID) references StudentInfo(StuID)

alter table Score
add constraint FK_ClassInfo_Score foreign key(ClassID) references ClassInfo(ClassID)


go
use master
create database 房屋出租系统
on
	(
	name='房屋出租系统',
	filename='F:\数据库文件\房屋出租系统.mdf',
	size=5,
	maxsize=9,
	filegrowth=3
	)
log on
	(
	name='房屋出租系统_log',
	filename='F:\数据库文件\房屋出租系统_log.ldf',
	size=5,
	maxsize=9,
	filegrowth=3
	)

	go
	use 房屋出租系统
	create table tblUser
	(
	userId int primary key identity(1,1) ,
	userName varchar(20) ,
	userTel char(11) check(userTel=11) not null 
	)


	create table tblHouseType
	(
	typeId int primary key identity(1,1) not null,
	typName varchar(40) not null
	)


	create table tblQx
	(
	qxId int primary key identity(1,1),
	qxName varchar(20) check(qxName='武昌' or qxName='汉阳' or qxName='汉口') not null
	)

	create table tblHouseInfo
	(
	Id int primary key identity(1,1),
	qxDescribe varchar(200) not null,
	userId int not null,
	Rent int not null,
	HouseRoom int not null,
	HouseHell int null,
	typeId int not null,
	qxId int not null
	)
	go
alter table tblHouseInfo
add constraint FK_tblUser_tblHouseInfo foreign key(userId) references tblUser(userId)

	go
alter table tblHouseInfo
add constraint FK_tblHouseType_tblHouseInfo foreign key(typeId) references tblHouseType(typeId)

	go
alter table tblHouseInfo
add constraint FK_tblQx_tblHouseInfo foreign key(qxId) references tblQx(qxId)
